package com.jiepos.mpos.biz.review.service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.jiepos.mpos.common.constant.DBConstant.Table;
import com.jiepos.mpos.common.constant.SystemConstant;
import com.jiepos.mpos.common.dao.SettleAccountDao;
import com.jiepos.mpos.common.entity.MemReviewEntity;
import com.jiepos.mpos.core.entity.Pagination;
import com.jiepos.mpos.core.service.BaseService;
import com.jiepos.mpos.core.util.StringUtils;

@Service
public class SettleAccountDoneService extends BaseService {

	@Autowired
	private SettleAccountDao settleAccountDao;
	
	public Map getAccountDoneListJson(Pagination pagination,HttpServletRequest request,MemReviewEntity memReviewEntity){
		
		StringBuffer sb = new StringBuffer();
		List list = new ArrayList<>();
		sb.append("select aa.*,bb.AUDIT_COMMENTS,cc.custom_no,cc.phone,cc. NAME,cc.realname_auth_st,cc.rec_st,")
			.append(" cc.certificate_no,cc.supcustom,cc.ins_uuid,cc.ins_cd,cc.ins_cn_nm,cc.insname,cc.areaname ")
			.append(" from (SELECT t1.audit_st,t1.bankcard_uuid AS uuid,t1.custom_uuid,TO_CHAR (t1.rec_crt_ts,'yyyy-mm-dd hh24:mi:ss') AS rec_crt_ts,")
			.append(" TO_CHAR (t1.rec_upd_ts,'yyyy-mm-dd hh24:mi:ss') AS rec_upd_ts,t1.rec_upd_usr_id FROM ").append(Table.T_APP_CUSTOM_BANKCARD)
			.append(" t1 where T1.AUDIT_ST in ('"+SystemConstant.REVIEW_ST_1+"','"+ SystemConstant.REVIEW_ST_2+"') and T1.ACCT_ATTR = ? and T1.rec_st != ? ) aa")
			.append(" LEFT JOIN (SELECT	BANKCARD_UUID,(SELECT	AUDIT_COMMENTS FROM( SELECT AUDIT_COMMENTS,SYSTEM_TYPE,SYSTEM_KEY,STATE_OPERATE	FROM ")
			.append(Table.T_APP_WORKFLOW_TRACE).append(" ORDER BY REC_CRT_TS DESC) WHERE SYSTEM_TYPE = 'T_APP_CUSTOM_BANKCARD'")
			.append(" and rec_st != '0' AND STATE_OPERATE IN ('"+SystemConstant.STATE_CHANGE_1+"','"+ SystemConstant.STATE_CHANGE_2+"') AND SYSTEM_KEY = BANKCARD_UUID AND ROWNUM <= 1) AS AUDIT_COMMENTS FROM ")
			.append(Table.T_APP_CUSTOM_BANKCARD).append(" ) bb ON aa.uuid = bb.BANKCARD_UUID LEFT JOIN (SELECT t2.CUSTOM_UUID,t2.CUSTOM_NO,CONCAT(RPAD (SUBSTR (t2.phone, 1, 3),LENGTH (t2.phone) - 4,'*'),SUBSTR (t2.phone,LENGTH (t2.phone) - 3,4)) phone,t2. NAME,")
			.append(" NVL2 (t3.login_id,CONCAT(RPAD (SUBSTR (t3.login_id, 1, 3),LENGTH (t3.login_id) - 4,'*'),SUBSTR (t3.login_id,LENGTH (t3.login_id) - 3,4)) || '-' || t3. NAME,'') AS SUPCUSTOM,t4.ins_uuid,t4.ins_cd,t4.ins_cn_nm,NVL2 (t4.ins_cd,t4.ins_cd || '-' || t4.ins_cn_nm,'') AS insname,")
			.append(" NVL2 (t5.area_cd,t5.area_cd || '-' || t5.area_cn_nm,'') AS areaname,t2.REALNAME_AUTH_ST,t2.REALNAME_AUDIT_ST,t2.REC_ST,CONCAT ( ")
			.append(" RPAD (SUBSTR (t2.CERTIFICATE_NO, 1, 6),LENGTH (t2.CERTIFICATE_NO) - 4,'*'),SUBSTR (t2.CERTIFICATE_NO,LENGTH (t2.CERTIFICATE_NO) - 6,4)) AS CERTIFICATE_NO FROM ")
			.append(Table.T_APP_CUSTOM_INF).append(" t2 LEFT JOIN "+Table.T_APP_CUSTOM_INF+" t3 ON t2.SUP_CUSTOM_UUID = t3.CUSTOM_UUID LEFT JOIN "+Table.T_APP_INS_INF+" t4 ON t2.ins_uuid = t4.ins_uuid ")
			.append(" LEFT JOIN "+Table.T_APP_AREA_CD+" t5 ON t2.area_uuid = t5.area_uuid where t2.realname_audit_st = ? and t2.rec_st != ?")
			.append(" ) cc ON aa.custom_uuid = cc.custom_uuid where 1=1 ");
			list.add(SystemConstant.ACCT_ATTR_0);
			list.add(SystemConstant.REC_ST_0);
			list.add(SystemConstant.REVIEW_ST_1);
			list.add(SystemConstant.REC_ST_0);
		if (!StringUtils.isBlank(memReviewEntity.getCustom_no())) {
			sb.append( " and cc.custom_no like ? " );
			list.add("%" + memReviewEntity.getCustom_no() + "%");
		}
		if (!StringUtils.isBlank(memReviewEntity.getPhone())) {
			sb.append( " and cc.phone like ? " );
			list.add("%" + memReviewEntity.getPhone() + "%");
		}
		if (!StringUtils.isBlank(memReviewEntity.getName())) {
			sb.append( " and cc.name like ? " );
			list.add("%" + memReviewEntity.getName() + "%");
		}
		if (!StringUtils.isBlank(memReviewEntity.getIns_cd())) {
			sb.append( " and cc.ins_cd like ? " );
			list.add("%" + memReviewEntity.getIns_cd() + "%");
		}
		if (!StringUtils.isBlank(memReviewEntity.getIns_cn_nm())) {
			sb.append( " and cc.ins_cn_nm like ? " );
			list.add("%" + memReviewEntity.getIns_cn_nm() + "%");
		}
		if (!StringUtils.isBlank(memReviewEntity.getCertificate_no())) {
			sb.append( " and cc.certificate_no like ? " );
			list.add("%" + memReviewEntity.getCertificate_no() + "%");
		}
		pagination.queryString = sb.toString();
		pagination = super.getCustomAuthSql(request, pagination, "cc", null);
		pagination.queryString = pagination.queryString+" order by aa.rec_upd_ts desc ";
		return settleAccountDao.getPaginationMap(pagination, "aa", list);
		
	}
}
